package com.dq.domain.base.impl;

import com.dq.domain.base.BaseDao;
import com.dq.domain.base.BaseEntity;
import com.dq.xss.SQLFilter;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.io.Serializable;
import java.util.List;
import java.util.Map;

@Service
public class BaseDaoImpl implements BaseDao {
    @PersistenceContext
    private EntityManager entityManager;



    public static final String HQL_GROUP = "GROUPBY";
    public static final String HQL_FUNCTION = "hqlFunctionExcel";


    public static final String likeSqlStr = "Like";
    public static final String gtSqlStr = "0gt0";
    public static final String ltSqlStr = "0lt0";
    public static final String gteSqlStr = "0gte0";
    public static final String lteSqlStr = "0lte0";
    public static final String inSqlStr = "0in0";

    public static final String orSqlStr = "0or0";

    // + and (key1=v1 OR key2=v2 OR key2=v3))
    public static final String orAndSqlStr = "0orAnd0";
    // + or key=v1 or key=v2 or key=v3
    public static final String orMultiSqlStr = "0orMulti0";


    /**
     * 公用
     * @param param
     * @param hql
     * @param queryIf if or not set value by Query
     * @return StringBuilder
     */
    private StringBuilder getHql(Map<String, Object> param, StringBuilder hql, boolean queryIf) {
        hql.append(" u where 1=1 ");
        Object deleted = param.get("deleted");
        if(deleted != null && deleted instanceof Boolean) {
            if(queryIf) {
                hql.append(" and u.deleted = :deleted ");
            }else {
                hql.append(" and u.deleted = ").append(deleted);
            }
        }else {
            hql.append(" and u.deleted = false");
        }

        for (String key : param.keySet()) {
            if(HQL_FUNCTION.equals(key)) {
//                hql.append(" and " + param.get(key).toString() + " ");
                hql.append(" " + param.get(key).toString() + " ");
                continue;
            }
            if ("deleted".equals(key) || "page".equals(key) || "size".equals(key) || "sidx".equals(key) || "order".equals(key) || HQL_GROUP.equals(key)) {
                continue;
            }

            String format = "";
            if(key.contains(orSqlStr)) {
                String keyValue;
                if(queryIf) {
                    keyValue = key;
                }else {
                    keyValue = param.get(key).toString();
                }
                format = String.format(" or u.%s%s ", getHqlKey(key.replace(orSqlStr, "")), keyValue);
            }else if(key.contains(orAndSqlStr)) {
                StringBuilder keyHql = new StringBuilder("");
                Object o = param.get(key);
                if(o instanceof Map) {
                    Map<String, Object> keyQuery = (Map<String, Object>) o;
                    Object[] keys = keyQuery.keySet().toArray();
                    for (int i = 0; i < keys.length; i++) {
                        String k = keys[i].toString();
                        String kValue;
                        if(queryIf) {
                            kValue = k;
                        }else {
                            kValue = keyQuery.get(k).toString();
                        }
                        if(i == 0) {
                            keyHql.append(String.format(" and (u.%s%s ", getHqlKey(k), kValue));
                        }else if(i == keys.length-1) {
                            keyHql.append(String.format(" or u.%s%s ) ", getHqlKey(k), kValue));
                        }else {
                            keyHql.append(String.format(" or u.%s%s ", getHqlKey(k), kValue));
                        }
                    }
                }
                format = keyHql.toString();
            }else if(key.contains(orMultiSqlStr)) {
                StringBuilder keyHql = new StringBuilder("");
                Object o = param.get(key);
                if(o instanceof List) {
                    List<String> list = (List<String>) o;
                    for (int i = 0; i < list.size(); i++) {
                        String kValue;
                        if(queryIf) {
                            kValue = key+i;
                        }else {
                            kValue = list.get(i);
                        }
                        keyHql.append(String.format(" or u.%s%s ", getHqlKey(key.replace(orMultiSqlStr, "")), kValue));
                    }
                }
                format = keyHql.toString();
            }
            else {
                String keyValue;
                if(queryIf) {
                    keyValue = key;
                }else {
                    keyValue = param.get(key).toString();
                }
                format = String.format(" and u.%s%s ", getHqlKey(key), keyValue);
            }

            hql.append(format);
        }
        return hql;
    }

    private String getHqlKey(String key) {
        return key.contains("_")
                ? key.contains(likeSqlStr)
                    ? key.replaceAll("_", ".").replace(likeSqlStr, " like :")
                    : key.contains(gtSqlStr)
                        ? key.replaceAll("_", ".").replace(gtSqlStr, " > :")
                        : key.contains(ltSqlStr)
                            ? key.replaceAll("_", ".").replace(ltSqlStr, "< :")
                            : key.contains(inSqlStr)
                                ? key.replaceAll("_", ".").replace(inSqlStr, " in :")
                                : key.contains(gteSqlStr)
                                    ? key.replaceAll("_", ".").replace(gteSqlStr, " >= :")
                                    : key.contains(lteSqlStr)
                                        ? key.replaceAll("_", ".").replace(lteSqlStr, " <= :")
                                        : key.replaceAll("_", ".") + " = :"
                : key.contains(likeSqlStr)
                    ? key.replace(likeSqlStr, " like :")
                    : key.contains(gtSqlStr)
                        ? key.replace(gtSqlStr, " > :")
                        : key.contains(ltSqlStr)
                            ? key.replace(ltSqlStr, " < :")
                            : key.contains(inSqlStr)
                                ? key.replace(inSqlStr, " in :")
                                : key.contains(gteSqlStr)
                                    ? key.replace(gteSqlStr, " >= :")
                                    : key.contains(lteSqlStr)
                                        ? key.replace(lteSqlStr, " <= :")
                                        : key + " = :";
    }

    @Override
    public String query2Hql(Class c, Map<String, Object> query) {
        StringBuilder hql = new StringBuilder("select u from " + c.getSimpleName());

        hql = getHql(query, hql, true);

        if (query.containsKey(HQL_GROUP)) {
            hql.append(" GROUP BY " + SQLFilter.sqlInject(query.get(HQL_GROUP) + ""));
        }

        if (query.containsKey("sidx")) {
            hql.append(" order by u." + SQLFilter.sqlInject(query.get("sidx") + ""));
            if (query.containsKey("order")) {
                hql.append(" " + SQLFilter.sqlInject(query.get("order") + ""));
            } else {
                hql.append(" asc ");
            }
        } else {
            hql.append(" order by u.id desc ");
        }

        System.out.println(hql.toString());

        return hql.toString();
    }
    @Override
    public String count2Hql(Class c, Map<String, Object> query) {
        StringBuilder hql = new StringBuilder("select count(u) from " + c.getSimpleName());
        hql = getHql(query, hql, true);

        if (query.containsKey(HQL_GROUP)) {
            hql.append(" GROUP BY " + SQLFilter.sqlInject(query.get(HQL_GROUP) + ""));
        }

        System.out.println(hql.toString());

        return hql.toString();
    }

    /**
     * perform complex arguments HQL, no page and by page
     */
    @SuppressWarnings("unchecked")
    @Override
    public <T extends Object> List<T> find(String hql, Map<String, Object> param) {
        Integer page = (Integer) param.get("page");
        Integer size = (Integer) param.get("size");

        Query query = this.entityManager.createQuery(hql);
        if (page == null) {
            setParamByQuery(param, query);

            return query.getResultList();
        } else {
            return find(hql, param, page, size);
        }
    }

    /**
     * perform complex arguments HQL, by page
     */
    @SuppressWarnings("unchecked")
    @Override
    public <T extends Object> List<T> find(String hql, Map<String, Object> param, Integer page, Integer rows) {
        if (page == null || page < 1) {
            page = 1;
        }
        if (rows == null || rows < 1) {
            rows = 10;
        }

        Query query = this.entityManager.createQuery(hql);

        setParamByQuery(param, query);

        return query.setFirstResult((page-1) * rows).setMaxResults(rows).getResultList();
    }
    @Override
    public Long count(String hql, Map<String, Object> param) {
        Query query = this.entityManager.createQuery(hql);

        setParamByQuery(param, query);

        Long count = 0L;
        if(query.getResultList() != null && query.getResultList().size() > 0) {
            Number number = (Number) query.getResultList().get(0);
            count = number.longValue();
        }

        return count;
    }

    /**
     * perform easy arguments HQL, no page
     */
    @SuppressWarnings("unchecked")
    @Override
    public <T extends Object> List<T> find(String hql, Object[] param) {
        Query q = this.entityManager.createQuery(hql);
        if (param != null && param.length > 0) {
            for (int i = 0; i < param.length; i++) {
                q.setParameter(i, param[i]);
            }
        }
        return q.getResultList();
    }
    /**
     * perform easy arguments HQL, by page
     */
    @SuppressWarnings("unchecked")
    @Override
    public <T extends Object> List<T> find(String hql, Object[] param, Integer page, Integer rows) {
        if (page == null || page < 1) {
            page = 1;
        }
        if (rows == null || rows < 1) {
            rows = 10;
        }
        Query q = this.entityManager.createQuery(hql);
        if (param != null && param.length > 0) {
            for (int i = 0; i < param.length; i++) {
                q.setParameter(i, param[i]);
            }
        }
        return q.setFirstResult((page-1) * rows).setMaxResults(rows).getResultList();
    }
    @Override
    public Long count(String hql, Object[] param) {
        Query q = this.entityManager.createQuery(hql);
        if (param != null && param.length > 0) {
            for (int i = 0; i < param.length; i++) {
                q.setParameter(i, param[i]);
            }
        }
        return new Long((long) q.getResultList().get(0));
    }

    /**
     * perform no arguments HQL
     */
    @SuppressWarnings("unchecked")
    @Override
    public <T extends Object> List<T> find(String hql) {
        return this.entityManager.createQuery(hql).getResultList();
    }
    public Long count(String hql) {
        return new Long((long) this.entityManager.createQuery(hql).getResultList().get(0));
    }


    /**
     *  perform complex arguments SQL, no page and by page
     */
    @SuppressWarnings("unchecked")
    @Override
    public List<Map> findBySql2Map(String sql, Map<String, Object> param) {
        Integer page = (Integer) param.get("page");
        Integer size = (Integer) param.get("size");

        Query query = this.entityManager.createNativeQuery(sql);
        if (page == null) {
            setParamByQuery(param, query);

            query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
            return query.getResultList();
        } else {
            return findBySql2Map(sql, param, page, size);
        }
    }
    /**
     *  perform complex arguments SQL, by page
     */
    @SuppressWarnings("unchecked")
    @Override
    public List<Map> findBySql2Map(String sql, Map<String, Object> param, Integer page, Integer rows) {
        if (page == null || page < 1) {
            page = 1;
        }
        if (rows == null || rows < 1) {
            rows = 10;
        }
        Query query = this.entityManager.createNativeQuery(sql);

        setParamByQuery(param, query);

        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return query.setFirstResult((page-1) * rows).setMaxResults(rows).getResultList();
    }

    /**
     *  perform complex arguments SQL, no page and by page
     */
    @SuppressWarnings("unchecked")
    @Override
    public <T extends Object> List<T> findBySql(Class c, String sql, Map<String, Object> param) {
        Integer page = (Integer) param.get("page");
        Integer size = (Integer) param.get("size");

        Query query = this.entityManager.createNativeQuery(sql, c);
        if (page == null) {
            setParamByQuery(param, query);

            return query.getResultList();
        } else {
            return findBySql(c, sql, param, page, size);
        }
    }
    /**
     *  perform complex arguments SQL, by page
     */
    @SuppressWarnings("unchecked")
    @Override
    public <T extends Object> List<T> findBySql(Class c, String sql, Map<String, Object> param, Integer page, Integer rows) {
        if (page == null || page < 1) {
            page = 1;
        }
        if (rows == null || rows < 1) {
            rows = 10;
        }
        Query query = this.entityManager.createNativeQuery(sql, c);

        setParamByQuery(param, query);

        return query.setFirstResult((page-1) * rows).setMaxResults(rows).getResultList();
    }

    /**
     * perform complex arguments SQL, 2map and 2class count
     */
    @Override
    public Long countBySql(String sql, Map<String, Object> param) {
        Query query = this.entityManager.createNativeQuery(sql);

        setParamByQuery(param, query);

        Long count = 0L;
        if(query.getResultList() != null && query.getResultList().size() > 0) {
            Number number = (Number) query.getResultList().get(0);
            count = number.longValue();
        }

        return count;
    }

    /**
     *  perform no arguments SQL
     */
    @SuppressWarnings("unchecked")
    @Override
    public <T extends Object> List<T> findBySql(Class c, String sql) {
        return this.entityManager.createNativeQuery(sql, c).getResultList();
    }
    @Override
    public Long countBySql(String sql) {
        return new Long((long) this.entityManager.createNativeQuery(sql).getResultList().get(0));
    }


    private void setParamByQuery(Map<String, Object> param, Query q) {
        if (param != null && param.keySet().size() > 0) {
            for (String key : param.keySet()) {
                if ("page".equals(key) || "size".equals(key) || "sidx".equals(key) || "order".equals(key) || HQL_GROUP.equals(key) || HQL_FUNCTION.equals(key)) {
                    continue;
                }
                if (key.contains(orAndSqlStr)) {
                    Object o = param.get(key);
                    if (o instanceof Map) {
                        Map<String, Object> keyQuery = (Map<String, Object>) o;
                        Object[] keys = keyQuery.keySet().toArray();
                        for (int i = 0; i < keys.length; i++) {
                            String k = keys[i].toString();
                            q.setParameter(k, keyQuery.get(k));
                        }
                    }
                } else if (key.contains(orMultiSqlStr)) {
                    Object o = param.get(key);
                    if (o instanceof List) {
                        List<String> list = (List<String>) o;
                        for (int i = 0; i < list.size(); i++) {
                            q.setParameter(key + i, list.get(i));
                        }
                    }
                } else {
                    q.setParameter(key, param.get(key));
                }
            }
        }
    }

    /**
     * get Oblect by id, HQL
     */
    @Override
    public <T extends Object> T get(Class<T> c, Serializable id) {
        return (T) this.entityManager.find(c, id);
    }
    /**
     * get Oblect by complex arguments, HQL
     */
    @Override
    public <T extends Object> T get(String hql, Map<String, Object> param) {
        List<T> l = this.find(hql, param, 0, 1);
        if (l != null && l.size() > 0) {
            return l.get(0);
        } else {
            return null;
        }
    }
    /**
     * get Oblect by easy arguments, HQL
     */
    @Override
    public <T extends Object> T get(String hql, Object[] param) {
        List<T> l = this.find(hql, param, 0, 1);
        if (l != null && l.size() > 0) {
            return l.get(0);
        } else {
            return null;
        }
    }

    @Override
    @Transactional
    public Integer executeHql(String hql) {
        return this.entityManager.createQuery(hql).executeUpdate();
    }

    @Override
    @Transactional
    public Integer executeHql(String hql, Object[] param) {
        Query q = this.entityManager.createQuery(hql);
        if (param != null && param.length > 0) {
            for (int i = 0; i < param.length; i++) {
                q.setParameter(i, param[i]);
            }
        }
        return q.executeUpdate();
    }

    @Override
    public Integer executeHql(String hql, Map<String, Object> param) {
        Query q = this.entityManager.createQuery(hql);
        if (param != null && param.keySet().size() > 0) {
            for (String key : param.keySet()) {
                if(key.contains(orAndSqlStr)) {
                    Object o = param.get(key);
                    if(o instanceof Map) {
                        Map<String, Object> keyQuery = (Map<String, Object>) o;
                        Object[] keys = keyQuery.keySet().toArray();
                        for (int i = 0; i < keys.length; i++) {
                            String k = keys[i].toString();
                            q.setParameter(k, keyQuery.get(k));
                        }
                    }
                }else if(key.contains(orMultiSqlStr)) {
                    Object o = param.get(key);
                    if(o instanceof List) {
                        List<String> list = (List<String>) o;
                        for (int i = 0; i < list.size(); i++) {
                            q.setParameter(key+i, list.get(i));
                        }
                    }
                }
                else {
                    q.setParameter(key, param.get(key));
                }
            }
        }
        return q.executeUpdate();
    }

    @Override
    public String queryAdminHql(Class c, Map<String, Object> query) {
        StringBuilder hql = new StringBuilder("select u from " + c.getSimpleName() + " u where u.deleted=false");
        StringBuilder end = new StringBuilder("");
        if (query.containsKey("sidx")) {
            end.append(" order by u." + SQLFilter.sqlInject(query.get("sidx") + ""));
            if (query.containsKey("order")) {
                end.append(" " + SQLFilter.sqlInject(query.get("order") + ""));
            } else {
                end.append(" asc ");
            }
        } else {
            end.append(" order by u.id desc ");
        }
        for (String key : query.keySet()) {
            if (key.equals("page") || key.equals("size") || key.equals("sidx") || key.equals("order")) {
                continue;
            }
            hql.append(" and u.");
            if (key.contains("_")) {
                if (key.contains("like")) {
                    hql.append(key.replaceAll("_", ".").replace("like", "") + " like:");
                } else {
                    hql.append(key.replaceAll("_", ".")).append("=:");
                }
            } else {
                if (key.contains("like")) {
                    hql.append(key.replace("like", "") + " like:");
                } else {
                    hql.append(key).append("=:");
                }
            }
            hql.append(key);
        }
        return hql.append(end).toString();
    }
    @Override
    public String countAdminHql(Class c, Map<String, Object> query) {
        StringBuilder hql = new StringBuilder("select count(u) from " + c.getSimpleName() + " u where u.deleted=false" +
                " ");
        StringBuilder end = new StringBuilder("");
        for (String key : query.keySet()) {
            if (key.equals("page") || key.equals("size") || key.equals("sidx") || key.equals("order")) {
                continue;
            }
            hql.append(" and u.");
            if (key.contains("_")) {
                if (key.contains("like")) {
                    hql.append(key.replaceAll("_", ".").replace("like", "") + " like:");
                } else {
                    hql.append(key.replaceAll("_", ".")).append("=:");
                }
            } else {
                if (key.contains("like")) {
                    hql.append(key.replace("like", "") + " like:");
                } else {
                    hql.append(key).append("=:");
                }
            }
            hql.append(key);
        }
        return hql.append(end).toString();
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public <T extends BaseEntity> void save(T o) {
        this.entityManager.persist(o);
        this.entityManager.flush();
    }
    @Override
    @Transactional(rollbackFor = Exception.class)
    public <T extends BaseEntity> void modify(T o) {
        this.entityManager.merge(o);
        this.entityManager.flush();
    }
    @Override
    public <T extends BaseEntity> void delete(T o) {
        o.setDeleted(true);
        this.entityManager.merge(o);
        this.entityManager.flush();
    }
    @Override
    public <T extends BaseEntity> void forceDelete(T o) {
        this.entityManager.remove(o);
        this.entityManager.flush();
    }
    /**
     * 单个 添加/修改
     * 手动管理事物
     * @param o
     * @param <T>
     */
    @Override
    public <T extends BaseEntity> void saveOrModify(T o) {
        // 手动开启事务
        TransactionStatus transactionStatus = dataSourceTransactionManager.getTransaction(transactionDefinition);
        try {
            if (o.getId() != null && this.entityManager.find(BaseEntity.class, o.getId()) != null) {
                this.entityManager.merge(o);
            } else {
                this.entityManager.persist(o);
            }
            this.entityManager.flush();

            //  手动提交事务
            dataSourceTransactionManager.commit(transactionStatus);
        }catch (Exception e) {
            e.printStackTrace();
            // 手动回滚事务, 最好是放在catch 里面,防止程序异常而事务一直卡在哪里未提交
            dataSourceTransactionManager.rollback(transactionStatus);
        }
    }

    /**
     * 批量添加/修改
     * 手动管理事物
     * @param list
     * @param <T>
     */
    @Override
    public <T extends BaseEntity> void saveOrModifyList(List<T> list) {
        // 手动开启事务
        TransactionStatus transactionStatus = dataSourceTransactionManager.getTransaction(transactionDefinition);
        try {
            for (T o : list) {
                if (o.getId() != null && this.entityManager.find(BaseEntity.class, o.getId()) != null) {
                    this.entityManager.merge(o);
                } else {
                    this.entityManager.persist(o);
                }
            }

            this.entityManager.flush();
            //  手动提交事务
            dataSourceTransactionManager.commit(transactionStatus);
        }catch (Exception e) {
            e.printStackTrace();
            // 手动回滚事务, 最好是放在catch 里面,防止程序异常而事务一直卡在哪里未提交
            dataSourceTransactionManager.rollback(transactionStatus);
        }
    }

    /**
     * 注入父接口
     *
     * 关于事务管理器，不管是JPA还是JDBC等都实现自接口 PlatformTransactionManager
     * 如果你添加的是 spring-boot-starter-jdbc 依赖，框架会默认注入 DataSourceTransactionManager 实例。
     * 如果你添加的是 spring-boot-starter-data-jpa 依赖，框架会默认注入 JpaTransactionManager 实例。
     */
    @Autowired
    PlatformTransactionManager dataSourceTransactionManager;
    @Autowired
    TransactionDefinition transactionDefinition;
}
